import pymysql
import json
import os
import time
from logger_config import logger
from contextlib import contextmanager

class Database:
    def __init__(self):
        self.host = os.getenv('MYSQL_HOST', 'localhost')
        self.user = os.getenv('MYSQL_USER', 'root')
        self.password = os.getenv('MYSQL_PASSWORD', 'Alex2252@')
        self.database = os.getenv('MYSQL_DATABASE', 'life_status')
        self.port = 3306
        self.charset = 'utf8mb4'
        self.max_retries = 5
        self.retry_interval = 5

    @contextmanager
    def get_connection(self):
        conn = None
        retries = 0
        while retries < self.max_retries:
            try:
                conn = pymysql.connect(
                    host=self.host,
                    user=self.user,
                    password=self.password,
                    database=self.database,
                    port=self.port,
                    charset=self.charset,
                    cursorclass=pymysql.cursors.DictCursor,
                    connect_timeout=10,
                    ssl={'ssl': {}}
                )
                yield conn
                break
            except Exception as e:
                retries += 1
                if retries == self.max_retries:
                    logger.error(f"数据库连接错误: {str(e)}")
                    raise
                logger.warning(f"数据库连接失败，{self.retry_interval}秒后重试 ({retries}/{self.max_retries}): {str(e)}")
                time.sleep(self.retry_interval)
            finally:
                if conn:
                    conn.close()

    def init_db(self):
        """初始化数据库表"""
        create_tables_sql = [
            """
            CREATE TABLE IF NOT EXISTS judge_list (
                id INT AUTO_INCREMENT PRIMARY KEY,
                item_name VARCHAR(100) NOT NULL,
                is_checkbox BOOLEAN NOT NULL,
                options JSON NOT NULL,
                scores JSON NOT NULL,
                enabled BOOLEAN NOT NULL DEFAULT TRUE
            ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
            """,
            """
            CREATE TABLE IF NOT EXISTS day_data (
                id INT AUTO_INCREMENT PRIMARY KEY,
                date DATE NOT NULL,
                item_id INT NOT NULL,
                selected_option VARCHAR(100) NOT NULL,
                score INT NOT NULL,
                FOREIGN KEY (item_id) REFERENCES judge_list(id)
            ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
            """
        ]

        with self.get_connection() as conn:
            with conn.cursor() as cursor:
                for sql in create_tables_sql:
                    cursor.execute(sql)
            conn.commit()

    def add_judge_item(self, item_name, is_checkbox, option_list):
        """添加评分项，option_list为[{id, option, score}, ...]"""
        with self.get_connection() as conn:
            with conn.cursor() as cursor:
                sql = """
                INSERT INTO judge_list (item_name, is_checkbox, options, scores)
                VALUES (%s, %s, %s, %s)
                """
                # scores字段冗余存空列表或提取option_list的score
                scores = [opt['score'] for opt in option_list]
                cursor.execute(sql, (item_name, is_checkbox, json.dumps(option_list, ensure_ascii=False), json.dumps(scores, ensure_ascii=False)))
            conn.commit()
            return cursor.lastrowid

    def get_all_judge_items(self, include_disabled=False):
        """获取所有评分项"""
        with self.get_connection() as conn:
            with conn.cursor() as cursor:
                try:
                    sql = "SELECT * FROM judge_list"
                    if not include_disabled:
                        sql += " WHERE enabled = TRUE"
                    cursor.execute(sql)
                    items = cursor.fetchall()
                    logger.info(f"从数据库获取到 {len(items)} 条记录")
                    
                    processed_items = []
                    for item in items:
                        try:
                            if not isinstance(item, dict):
                                logger.error(f"记录不是字典类型: {type(item)}")
                                continue
                                
                            processed_item = {
                                'id': item.get('id'),
                                'item_name': item.get('item_name'),
                                'is_checkbox': bool(item.get('is_checkbox')),
                                'enabled': bool(item.get('enabled', True)),
                                'options': [],
                                'scores': []
                            }
                            
                            options_data = item.get('options')
                            if isinstance(options_data, str):
                                try:
                                    processed_item['options'] = json.loads(options_data)
                                except json.JSONDecodeError as e:
                                    logger.error(f"解析options JSON失败: {str(e)}")
                                    processed_item['options'] = []
                            elif isinstance(options_data, (list, tuple)):
                                processed_item['options'] = list(options_data)
                            else:
                                logger.error(f"未知的options数据类型: {type(options_data)}")
                                processed_item['options'] = []
                            
                            scores_data = item.get('scores')
                            if isinstance(scores_data, str):
                                try:
                                    processed_item['scores'] = json.loads(scores_data)
                                except json.JSONDecodeError as e:
                                    logger.error(f"解析scores JSON失败: {str(e)}")
                                    processed_item['scores'] = []
                            elif isinstance(scores_data, (list, tuple)):
                                processed_item['scores'] = list(scores_data)
                            else:
                                logger.error(f"未知的scores数据类型: {type(scores_data)}")
                                processed_item['scores'] = []
                            
                            processed_items.append(processed_item)
                            
                        except Exception as e:
                            logger.error(f"处理记录时出错: {str(e)}")
                            continue
                    
                    logger.info(f"成功处理 {len(processed_items)} 条记录")
                    return processed_items
                    
                except Exception as e:
                    logger.error(f"获取评分项时发生错误: {str(e)}")
                    logger.exception("详细错误信息：")
                    return []

    def update_items_status(self, item_ids, enabled):
        """更新评分项状态"""
        with self.get_connection() as conn:
            with conn.cursor() as cursor:
                try:
                    sql = "UPDATE judge_list SET enabled = %s WHERE id IN (%s)"
                    format_strings = ','.join(['%s'] * len(item_ids))
                    sql = sql % ('%s', format_strings)
                    cursor.execute(sql, [enabled] + item_ids)
                    conn.commit()
                    return True
                except Exception as e:
                    logger.error(f"更新评分项状态失败: {str(e)}")
                    return False

    def delete_items(self, item_ids):
        """删除评分项"""
        with self.get_connection() as conn:
            with conn.cursor() as cursor:
                try:
                    # 首先删除相关的评分数据
                    sql = "DELETE FROM day_data WHERE item_id IN (%s)"
                    format_strings = ','.join(['%s'] * len(item_ids))
                    sql = sql % format_strings
                    cursor.execute(sql, item_ids)
                    
                    # 然后删除评分项
                    sql = "DELETE FROM judge_list WHERE id IN (%s)"
                    cursor.execute(sql, item_ids)
                    
                    conn.commit()
                    return True
                except Exception as e:
                    logger.error(f"删除评分项失败: {str(e)}")
                    return False

    def add_day_data(self, date, item_id, selected_option, score):
        """添加每日评分数据"""
        with self.get_connection() as conn:
            with conn.cursor() as cursor:
                sql = """
                INSERT INTO day_data (date, item_id, selected_option, score)
                VALUES (%s, %s, %s, %s)
                """
                cursor.execute(sql, (date, item_id, selected_option, score))
            conn.commit()
            return cursor.lastrowid

    def get_day_data(self, date):
        """获取指定日期的评分数据"""
        with self.get_connection() as conn:
            with conn.cursor() as cursor:
                sql = """
                SELECT d.*, j.item_name 
                FROM day_data d
                JOIN judge_list j ON d.item_id = j.id
                WHERE d.date = %s
                """
                cursor.execute(sql, (date,))
                return cursor.fetchall()

# 创建全局数据库实例
db = Database() 